/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package helpdesk;

import java.util.ArrayList;

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.util.Date;
import java.sql.Timestamp;

/**
 * This represents a message in our chat.
 *
 * @author brian
 */
public class ChatStatement {
    int id;
    int userId;
    String user; // user's name
    String message;
    Timestamp created;

    /**
     * Get the name of the user that created this message.
     *
     * @return
     */
    public String getUser() {
        return user;
    }

    
    /**
     * Get the date and time this message was created.
     *
     * Cannot be changed.
     *
     * @return
     */
    public Timestamp getCreated() {
        return created;
    }

    /**
     * Get the unique integer id of this message.
     *
     * Cannot be changed.
     *
     * @return
     */
    public int getId() {
        return id;
    }

    /**
     * Get the contents of this message.
     * @return
     */
    public String getMessage() {
        return message;
    }

    /**
     * Set the contents of this message. Does not actually update the database.
     * @param message
     */
    public void setMessage(String message) {
        this.message = message;
    }

    /**
     * Get the id of the user that created this message.
     *
     * @return
     */
    public int getUserId() {
        return userId;
    }

    /**
     * Set the id of the user that created this message. Does not update the
     * database.
     * @param userId
     */
    public void setUserId(int userId) {
        this.userId = userId;
    }

    /**
     * Return an arraylist of the emails of the users that have submitted a chat
     * statement after the time in ts.
     * @param ts
     * @return
     * @throws SQLException
     */
    public static ArrayList<String> getRecentChatters(Timestamp ts) throws SQLException {
        ArrayList<String> chatters = null;
        Connection conn = null;
        Statement stmt;
        ResultSet rs;
        
        try {
            conn = DBManager.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(
                    "SELECT DISTINCT users.email FROM chat INNER JOIN users ON " +
                    "chat.userid=users.userid WHERE chat.created >= '" + ts +
                    "' ORDER BY users.email"
                    );

            chatters = new ArrayList<String>();
            while (rs.next()) {
                chatters.add(rs.getString(1));
            }
        } finally {
            if (conn != null) conn.close();
        }
        
        return chatters;
    }

    /**
     * Get all chat statements created after ts with an ID greater than
     * lastId
     *
     * @param date
     * @return
     */
    public static ArrayList<ChatStatement> getChatStatements(int lastId, Timestamp ts) throws SQLException
    {
        Connection conn = null;
        Statement stmt;
        ResultSet rs;
        ArrayList<ChatStatement> chatStatements = null;

        try
        {
            conn = DBManager.getConnection();
            stmt = conn.createStatement();
            
            rs = stmt.executeQuery(
                    "SELECT chat.chatid, chat.userid, users.email, chat.message, chat.created " +
                    "FROM chat INNER JOIN users ON chat.userid=users.userid WHERE chat.chatid > " +
                    lastId + " AND chat.created >= '" + ts + "' ORDER BY chat.chatid"
                    );

            chatStatements = new ArrayList<ChatStatement>();
            while (rs.next())
            {
                ChatStatement chatStatement = new ChatStatement();
                chatStatement.id = rs.getInt(1);
                chatStatement.userId = rs.getInt(2);
                chatStatement.user = rs.getString(3);
                chatStatement.message = rs.getString(4);
                chatStatement.created = rs.getTimestamp(5);
                chatStatements.add(chatStatement);
            }
        }
        finally
        {
            if (conn != null) conn.close();
        }

        return chatStatements;
    }

    /**
     * Add this ChatStatement to the database.
     * 
     * @throws SQLException
     */
    public void create() throws SQLException
    {
        Connection conn = null;
        PreparedStatement ps;

        created = new Timestamp(new Date().getTime());

        try
        {
            conn = DBManager.getConnection();
            ps = conn.prepareStatement(
                    "INSERT INTO chat (userid, message, created) VALUES (?,?,?)"
                    );

            ps.setInt(1, userId);
            ps.setString(2, message);
            ps.setTimestamp(3, created);
            ps.executeUpdate();
        }
        finally
        {
            if (conn != null) conn.close();
        }
    }
}
